package com.hly.tool.excel;

import com.matrixone.apps.domain.util.MapList;
import com.matrixone.apps.framework.ui.UIUtil;
import matrix.util.StringList;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;

import java.io.*;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author ：Jack Hao
 * @date ：Created in 2021/7/16 11:16
 * @description：
 */
public class Excel
{

    /**
     * 获取excel 第一个sheet中的数据
     *
     * @param
     * @param filePath
     * @return
     * @throws Exception
     */
    public static List<MapList> getExcelData(String filePath) throws Exception
    {
        return getExcelData(new File(filePath));
    }

    /**
     * 获取excel 第一个sheet中的数据
     *
     * @param
     * @param filePath
     * @return
     * @throws Exception
     */
    public static List<MapList> getSheetData(String filePath, String sheetName) throws Exception
    {
        return getSheetData(new File(filePath), sheetName);
    }


    /**
     * 获取excel 第一个sheet中的数据
     * 获取excel 第一个sheet中的数据
     *
     * @param
     * @param file
     * @return
     * @throws Exception
     */
    public static List<MapList> getExcelData(File file) throws Exception
    {

        InputStream is = null;
        Workbook wb = null;
        List<MapList> result = new ArrayList<MapList>();
        is = new FileInputStream(file);
        wb = WorkbookFactory.create(is);
        int sheets = wb.getNumberOfSheets();
        for (int i = 0; i < sheets; i++)
        {
            Sheet sheet_i = wb.getSheetAt(i);
            MapList excelDataMapList = getExcelMapList(sheet_i);
            result.add(excelDataMapList);
        }
        try
        {
            if (is != null)
            {
                try
                {
                    is.close();
                } catch (IOException e)
                {
                    e.printStackTrace();
                }
            }

            if (wb != null)
            {
                wb = null;
            }
        } catch (Exception e)
        {
            e.printStackTrace();
        }

        return result;
    }

    /**
     * 获取excel 第一个sheet中的数据
     *
     * @param
     * @param file
     * @return
     * @throws Exception
     */
    public static List<MapList> getSheetData(File file, String sheetName) throws Exception
    {

        InputStream is = null;
        Workbook wb = null;
        List<MapList> result = new ArrayList<MapList>();
        is = new FileInputStream(file);
        wb = WorkbookFactory.create(is);
        int sheets = wb.getNumberOfSheets();
        for (int i = 0; i < sheets; i++)
        {
            Sheet sheet_i = wb.getSheetAt(i);
            String sheetName_i = sheet_i.getSheetName();
            if (UIUtil.isNotNullAndNotEmpty(sheetName) && UIUtil.isNotNullAndNotEmpty(sheetName_i) && sheetName_i.equals(sheetName))
            {
                MapList excelDataMapList = getExcelMapList(sheet_i);
                result.add(excelDataMapList);
            }

        }
        try
        {
            if (is != null)
            {
                try
                {
                    is.close();
                } catch (IOException e)
                {
                    e.printStackTrace();
                }
            }

            if (wb != null)
            {
                wb = null;
            }
        } catch (Exception e)
        {
            e.printStackTrace();
        }

        return result;
    }


    /**
     * 获取excel 从指定行号开始的数据
     *
     * @param
     * @param file
     * @param startRow
     * @return
     * @throws Exception
     */
    public static List<List<List<String>>> getExcelData(File file, Integer startRow) throws Exception
    {

        InputStream is = null;
        Workbook wb = null;
        List<List<List<String>>> result = new ArrayList<List<List<String>>>();
        is = new FileInputStream(file);
        wb = WorkbookFactory.create(is);
        int sheets = wb.getNumberOfSheets();
        for (int i = 0; i < sheets; i++)
        {
            Sheet sheet_i = wb.getSheetAt(i);
            List<List<String>> excelMapList = getExcelMapList(sheet_i, startRow);
            result.add(excelMapList);
        }
        try
        {
            if (is != null)
            {
                try
                {
                    is.close();
                } catch (IOException e)
                {
                    e.printStackTrace();
                }
            }

            if (wb != null)
            {
                wb = null;
            }
        } catch (Exception e)
        {
            e.printStackTrace();
        }

        return result;
    }


    /**
     * 获取excel 第一个sheet中的数据
     *
     * @param
     * @param filePath
     * @return
     * @throws Exception
     */
    public static MapList getExcel0Data(String filePath) throws Exception
    {
        return getExcel0Data(new File(filePath));
    }


    /**
     * 获取excel 第一个sheet中的数据
     *
     * @param
     * @param file
     * @return
     * @throws Exception
     */
    public static MapList getExcel0Data(File file) throws Exception
    {

        InputStream is = null;
        Workbook wb = null;
        Sheet sheet = null;
        MapList excelDataMapList = new MapList();
        String filePath = file.getAbsolutePath();
        is = new FileInputStream(new File(filePath));
        wb = WorkbookFactory.create(is);
        sheet = wb.getSheetAt(0);
        excelDataMapList = getExcelMapList(sheet);
        try
        {
            if (is != null)
            {
                try
                {
                    is.close();
                } catch (IOException e)
                {
                    e.printStackTrace();
                }
            }

            if (wb != null)
            {
                wb = null;
            }
        } catch (Exception e)
        {
            e.printStackTrace();
        }

        return excelDataMapList;
    }


    /**
     * 获取 excle 中的数据
     *
     * @param sheet
     * @return
     * @throws Exception
     */
    private static MapList getExcelMapList(Sheet sheet) throws Exception
    {
        MapList mapList = new MapList();

        if (sheet == null)
        {
            return mapList;
        }

        int rows = sheet.getPhysicalNumberOfRows();
        if (rows <= 0)
        {
            return mapList;
        }
        Row row0 = sheet.getRow(0);
        int colunms = row0.getPhysicalNumberOfCells();

        StringList headers = new StringList();

        //get headers
        for (int j = 0; j < colunms; j++)
        {
            Cell cell_i_j = row0.getCell(j);
            String header_i = getCellValue(cell_i_j);
            headers.add(header_i);
        }


        //get row values
        for (int i = 1; i < rows; i++)
        {
            //////System.out.println("i = " + i);
            Row row_i = sheet.getRow(i);
            Map<String, String> map = new HashMap<String, String>();

            //get start colunm
            for (int j = 0; j < colunms; j++)
            {
                Cell cell_i_j = row_i.getCell(j);
                String cellValue = getCellValue(cell_i_j);
                String header = headers.get(j);
                map.put(header, cellValue);
            }
            mapList.add(map);
        }

        return mapList;

    }


    /**
     * 获取 excle 中的数据
     *
     * @param sheet
     * @return
     * @throws Exception
     */
    private static List<List<String>> getExcelMapList(Sheet sheet, Integer startRow) throws Exception
    {
        List<List<String>> result = new ArrayList<List<String>>();

        if (sheet == null)
        {
            return result;
        }

//        int rows = sheet.getPhysicalNumberOfRows();
        int rows = sheet.getLastRowNum();
        if (rows <= startRow)
        {
            return result;
        }

        //get row values
        for (int i = startRow; i <= rows; i++)
        {
            //////System.out.println("i = " + i);
            Row row_i = sheet.getRow(i);
            int colunms_i = row_i.getPhysicalNumberOfCells();

            List<String> rowList = new ArrayList<String>();

            //get start colunm
            for (int j = 0; j < colunms_i; j++)
            {
                Cell cell_i_j = row_i.getCell(j);
                String cellValue = getCellValue(cell_i_j);
                rowList.add(cellValue);
            }
            result.add(rowList);
        }
        return result;
    }


    /**
     * @param row
     * @param column
     * @return
     */
    public String getCellValue(Row row, int column) throws Exception
    {
        if (row == null)
        {
            return "";
        }
        Cell cell = row.getCell(column);
        String cellValue = getCellValue(cell);
        return cellValue;
    }


    /**
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell) throws Exception
    {
        SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");
        Object val = "";
        try
        {
            if (cell != null)
            {
                if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
                {
                    val = cell.getNumericCellValue();
                    if (HSSFDateUtil.isCellDateFormatted(cell))
                    {
                        val = DateUtil.getJavaDate((Double) val); // POI Excel date
                        val = sdf.format((Date) val);
//                        //////System.out.println("val = "+val);
                    } else
                    {
                        if ((Double) val % 1 > 0)
                        {
                            val = new BigDecimal(val.toString());
                        } else
                        {
                            val = new DecimalFormat("0").format(val);
                        }
                    }
                } else if (cell.getCellType() == Cell.CELL_TYPE_STRING)
                {
                    val = cell.getStringCellValue();
                } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN)
                {
                    val = cell.getBooleanCellValue();
                } else if (DateUtil.isCellDateFormatted(cell))
                {
                    val = cell.getDateCellValue();
                    if (val != null)
                    {
                        val = sdf.format((Date) val);
                    }
                }
            }

        } catch (Exception e)
        {
            e.printStackTrace();
        }
        if (val == null || val.toString().trim().equals(""))
        {
            val = "";
        }
        return String.valueOf(val);
    }


    /**
     * 获取excel中的所有数据 后续需要自行提取头部信息
     *
     * @param file
     * @return
     */
    public static List<List<List<String>>> getDataNoHeader(File file) throws Exception
    {
        InputStream is = null;
        Workbook wb = null;
        List<List<List<String>>> result = new ArrayList<List<List<String>>>();
        is = new FileInputStream(file);
        wb = WorkbookFactory.create(is);
        int sheets = wb.getNumberOfSheets();
        for (int i = 0; i < sheets; i++)
        {
            Sheet sheet_i = wb.getSheetAt(i);
            List<List<String>> excelNoHeaderList = getExcelNoHeaderList(sheet_i);
            result.add(excelNoHeaderList);
        }
        try
        {
            if (is != null)
            {
                try
                {
                    is.close();
                } catch (IOException e)
                {
                    e.printStackTrace();
                }
            }

            if (wb != null)
            {
                wb = null;
            }
        } catch (Exception e)
        {
            e.printStackTrace();
        }

        return result;
    }


    /**
     * 获取excel中的所有数据 后续需要自行提取头部信息
     *
     * @param filePath
     * @return
     */
    public static List<List<List<String>>> getDataNoHeader(String filePath) throws Exception
    {
        return getDataNoHeader(new File(filePath));
    }


    /**
     * 获取 excle 中的数据
     *
     * @param sheet
     * @return
     * @throws Exception
     */
    private static List<List<String>> getExcelNoHeaderList(Sheet sheet) throws Exception
    {
        List<List<String>> result = new ArrayList<List<String>>();

        if (sheet == null)
        {
            return result;
        }

        int rows = sheet.getPhysicalNumberOfRows();
        if (rows <= 0)
        {
            return result;
        }

        Row row0 = sheet.getRow(0);
        int colunms = row0.getPhysicalNumberOfCells();


        //get row values
        for (int i = 0; i < rows; i++)
        {
            //////System.out.println("i = " + i);
            Row row_i = sheet.getRow(i);
            Map<String, String> map = new HashMap<String, String>();

            List<String> list_i = new ArrayList<String>();

            //get start colunm
            for (int j = 0; j < colunms; j++)
            {
                Cell cell_i_j = row_i.getCell(j);
                String cellValue = getCellValue(cell_i_j);
                list_i.add(cellValue);
            }
            result.add(list_i);
        }
        return result;
    }

    /**
     * 获取所有的表头
     *
     * @param file
     * @return
     */
    public static List getHeadersList(File file) throws Exception
    {
        List<StringList> result = new ArrayList<StringList>();
        InputStream is = null;
        Workbook wb = null;
        is = new FileInputStream(file);
        wb = WorkbookFactory.create(is);
        int sheets = wb.getNumberOfSheets();
        for (int i = 0; i < sheets; i++)
        {
            Sheet sheet_i = wb.getSheetAt(i);
            StringList headers_i = new StringList();

            int rows = sheet_i.getPhysicalNumberOfRows();
            if (rows > 0)
            {
                Row row0 = sheet_i.getRow(0);
                int colunms = row0.getPhysicalNumberOfCells();
                //get headers
                for (int j = 0; j < colunms; j++)
                {
                    Cell cell_i_j = row0.getCell(j);
                    String header_i = getCellValue(cell_i_j);
                    headers_i.add(header_i);
                }

            }
            result.add(headers_i);
        }
        try
        {
            if (is != null)
            {
                try
                {
                    is.close();
                } catch (IOException e)
                {
                    e.printStackTrace();
                }
            }

            if (wb != null)
            {
                wb = null;
            }
        } catch (Exception e)
        {
            e.printStackTrace();
        }
        return result;
    }


    /**
     * 获取所有的表头
     *
     * @param filePath
     * @return
     */
    public static List getHeadersList(String filePath) throws Exception
    {
        return getHeadersList(new File(filePath));
    }


    /**
     * 获取第一个sheet的表头
     *
     * @param file
     * @return
     */
    public static StringList getHeaders0(File file) throws Exception
    {
        StringList headers = new StringList();
        InputStream is = null;
        Workbook wb = null;
        is = new FileInputStream(file);
        wb = WorkbookFactory.create(is);
        int sheets = wb.getNumberOfSheets();

        if (sheets <= 0)
        {
            return headers;
        } else
        {
            Sheet sheet_0 = wb.getSheetAt(0);

            Row row0 = sheet_0.getRow(0);
            int colunms = row0.getPhysicalNumberOfCells();
            //get headers
            for (int j = 0; j < colunms; j++)
            {
                Cell cell_i_j = row0.getCell(j);
                String header_i = getCellValue(cell_i_j);
                headers.add(header_i);
            }
        }
        try
        {
            if (is != null)
            {
                try
                {
                    is.close();
                } catch (IOException e)
                {
                    e.printStackTrace();
                }
            }

            if (wb != null)
            {
                wb = null;
            }
        } catch (Exception e)
        {
            e.printStackTrace();
        }
        return headers;
    }

    /**
     * 获取第一个sheet的表头
     *
     * @param filePath
     * @return
     */
    public static StringList getHeaders0(String filePath) throws Exception
    {
        return getHeaders0(new File(filePath));
    }
}
